package com.base.cn.platform.os.controller.truncate;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * <h1>对数据库工具类</h1>
 * <p>该类是一个工具类，用于对比两个数据库的表的差异、及两个数据库中共有表的字段的差异。</p>
 * <p>在运行前只需要修改数据库名、IP、账号、密码等，修改完后直接运行便可在控制台中看到输出结果。</p>
 * <p>该方法只能在本地运行，如果客户的数据库在线，那可先将客户的数据导入到本地中再进行对比</p>
 * @author s.li
 * @date 2019/03/13
 */
public class ComparisonDB {

    //======================================修改，开始================================
    //原数据库名
    private static String oldDBName = "os-base";
    //原数据库配置
    private static String oldUrl = "jdbc:mysql://192.168.1.11:3306/"+oldDBName+"?useUnicode=true&characterEncoding=UTF-8";
    private static String oldUsername = "admin268xue";
    private static String oldPassword = "admin268xue";

    //目标数据名
    private static String targetDBName = "os-base-1";
    //目标数据配置
    private static String targetUrl = "jdbc:mysql://192.168.1.11:3306/"+targetDBName+"?useUnicode=true&characterEncoding=UTF-8";
    private static String targetUsername = "admin268xue";
    private static String targetPassword = "admin268xue";
    //=========================================修改，结束=============================

    //数据库驱动
    private static String driverClassName = "com.mysql.jdbc.Driver";

    public static void main(String[] args) throws Exception{
        // 动态加载mysql驱动
        Class.forName(driverClassName);

        //链接原数据库
        Connection oldConnection = DriverManager.getConnection(oldUrl,oldUsername,oldPassword);
        //获取原数据库结构
        DatabaseMetaData oldMetaData = oldConnection.getMetaData();
        //获取原数据中所有的表名
        ResultSet oldResultSet = oldMetaData.getTables(null,"%","%",new String[]{"TABLE"});
        //原数据库所有表名列表
        List<String> oldTableNameList = new ArrayList<>();
        while (oldResultSet.next()){
            String tableName = oldResultSet.getString("TABLE_NAME");
            oldTableNameList.add(tableName.trim());
        }
        //链接目标数据库
        Connection targetConnection = DriverManager.getConnection(targetUrl,targetUsername,targetPassword);
        //获取目标数据库结构
        DatabaseMetaData targetMetaData = targetConnection.getMetaData();
        //获取目标数据中所有的表名
        ResultSet targetResultSet = targetMetaData.getTables(null,"%","%",new String[]{"TABLE"});
        //目标数据库所有表名列表
        List<String> targetTableNameList = new ArrayList<>();
        while (targetResultSet.next()){
            String tableName = targetResultSet.getString("TABLE_NAME");
            targetTableNameList.add(tableName.trim());
        }
        System.out.println("==========================开始对比表=====================================");
        //非共有的表名
        List<String> notShareTableList = new ArrayList<>();
        oldTableNameList.forEach(tableName->{
            if(!targetTableNameList.contains(tableName.trim())){
                System.out.println("目标数据库"+targetDBName+"中不存在的表："+tableName.trim());
                notShareTableList.add(tableName);
            }
        });

        targetTableNameList.forEach(tableName->{
            if(!oldTableNameList.contains(tableName.trim())){
                System.out.println("原数据库"+oldDBName+"中不存在的表："+tableName.trim());
                notShareTableList.add(tableName);
            }
        });
        System.out.println("==========================对比表结束=====================================");

        System.out.println("==========================开始对比表的字段=====================================");
        oldTableNameList.forEach(tableName->{
            if(!notShareTableList.contains(tableName)){
                try {
                    //获取原数据表中的所有字段
                    Statement oldStatement = oldConnection.createStatement();
                    ResultSet oldResults = oldStatement.executeQuery("select * from `"+tableName+"` limit 0");
                    ResultSetMetaData oldSetMetaData = oldResults.getMetaData();
                    int oldCount = oldSetMetaData.getColumnCount();
                    List<String> oldColumnNameList = new ArrayList<>();
                    for (int i = 0; i < oldCount; i++) {
                        String columnName = oldSetMetaData.getColumnName(i+1);
                        oldColumnNameList.add(columnName);
                    }
                    //获取目标数据表中的所有字段
                    Statement targetStatement = targetConnection.createStatement();
                    ResultSet targetResults = targetStatement.executeQuery("select * from `"+tableName+"` limit 0");
                    ResultSetMetaData targetSetMetaData = targetResults.getMetaData();
                    int targetCount = targetSetMetaData.getColumnCount();
                    List<String> targetColumnNameList = new ArrayList<>();
                    for (int i = 0; i < targetCount; i++) {
                        String columnName = targetSetMetaData.getColumnName(i+1);
                        targetColumnNameList.add(columnName);
                    }
                    oldColumnNameList.forEach(columnName->{
                        if(!targetColumnNameList.contains(columnName)){
                            System.out.println("目标数据库"+targetDBName+"的"+tableName+"表中没有："+columnName+"字段");
                        }
                    });
                    targetColumnNameList.forEach(columnName->{
                        if(!oldColumnNameList.contains(columnName)){
                            System.out.println("原数据库"+oldDBName+"的"+tableName+"表中没有："+columnName+"字段");
                        }
                    });
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        });
        System.out.println("==========================对比表的字段结束=====================================");
        oldConnection.close();
        targetConnection.close();
    }
}
